﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Security.Policy;
using System.Threading;
using System.Web;
using System.Web.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;

namespace _MVC导入导出EXCEL.Util
{
    public class ExcelUtil
    {

        /// <summary>
        /// 使用NPOI方式导出数据
        /// </summary>
        /// <param name="dataSource"></param>
        /// <param name="excelName"></param>
        public static Stream DownLoadExcelOld(DataTable dataSource)
        {
            MemoryStream ms = new MemoryStream();

            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            IRow headerRows = sheet.CreateRow(0);
            //给Sheet添加表头
            for (int i = 0; i < dataSource.Columns.Count; i++)
            {
                headerRows.CreateCell(i).SetCellValue(dataSource.Columns[i].ColumnName);
            }
            //给Sheet写入数据
            for (int i = 0; i < dataSource.Rows.Count; i++)
            {
                IRow bodyRows = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataSource.Columns.Count; j++)
                {
                    bodyRows.CreateCell(j).SetCellValue(dataSource.Rows[i][j].ToString());
                }
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            Stream newStream = DecompressStream(ms, StreamType.Old);
            return newStream;
        }

        /// <summary>
        /// 使用NPOI方式导出数据
        /// </summary>
        /// <param name="dataSource"></param>
        /// <param name="excelName"></param>
        public static Stream DownLoadExcelNew(DataTable dataSource)
        {
            NPOIMemoryStream ms = new NPOIMemoryStream();

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            IRow headerRows = sheet.CreateRow(0);
            //给Sheet添加表头
            for (int i = 0; i < dataSource.Columns.Count; i++)
            {
                headerRows.CreateCell(i).SetCellValue(dataSource.Columns[i].ColumnName);
            }
            //给Sheet写入数据
            for (int i = 0; i < dataSource.Rows.Count; i++)
            {
                IRow bodyRows = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataSource.Columns.Count; j++)
                {
                    bodyRows.CreateCell(j).SetCellValue(dataSource.Rows[i][j].ToString());
                }
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            Stream newStream = DecompressStream(ms, StreamType.New);
            return newStream;
        }


        /// <summary>
        /// 压缩成Zip文件
        /// </summary>
        /// <param name="sourceStream"></param>
        /// <param name="streamType"></param>
        /// <returns></returns>
        public static Stream DecompressStream(Stream sourceStream, StreamType streamType)
        {
            if (streamType == StreamType.New)
            {
                NPOIMemoryStream responseStream = new NPOIMemoryStream();
                using (System.IO.Compression.GZipStream compressedStream = new System.IO.Compression.GZipStream(responseStream, System.IO.Compression.CompressionMode.Compress, true))
                {
                    byte[] buffer = new byte[sourceStream.Length];
                    int checkCounter = sourceStream.Read(buffer, 0, buffer.Length);
                    if (checkCounter != buffer.Length) throw new ApplicationException();
                    compressedStream.Write(buffer, 0, buffer.Length);
                }
                responseStream.Position = 0;
                return responseStream;
            }
            else
            {
                MemoryStream responseStream = new MemoryStream();
                using (System.IO.Compression.GZipStream compressedStream = new System.IO.Compression.GZipStream(responseStream, System.IO.Compression.CompressionMode.Compress, true))
                {
                    byte[] buffer = new byte[sourceStream.Length];
                    int checkCounter = sourceStream.Read(buffer, 0, buffer.Length);
                    if (checkCounter != buffer.Length) throw new ApplicationException();
                    compressedStream.Write(buffer, 0, buffer.Length);
                }
                responseStream.Position = 0;
                return responseStream;
            }

        }


        public static Stream RenderDataTableToExcel(DataTable SourceTable)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            IRow headerRow = sheet.CreateRow(0);

            // handling header. 
            foreach (DataColumn column in SourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value. 
            int rowIndex = 1;

            foreach (DataRow row in SourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in SourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            sheet = null;
            headerRow = null;
            workbook = null;

            return ms;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ExcelFileStream"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="headerRowIndex"></param>
        /// <param name="streamType"></param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, StreamType streamType, int sheetIndex = 0, int headerRowIndex = 0)
        {
            IWorkbook workbook;
            if (streamType == StreamType.New)
            {
                workbook = new XSSFWorkbook(ExcelFileStream);
            }
            else
            {
                workbook = new HSSFWorkbook(ExcelFileStream);
            }
            ISheet sheet = workbook.GetSheetAt(sheetIndex);

            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)

                        dataRow[j] = row.GetCell(j).ToString();
                }

                table.Rows.Add(dataRow);
            }

            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

    }
}